package com.example.email.mapper;

import com.example.email.entity.Mail;
import com.example.email.entity.Mailexplain;
import org.apache.ibatis.annotations.*;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Mapper
public interface MailMapper {

    /**
     * 回收站未读邮件数量
     * @param
     * @return 返回收信数量
     */
    @Select("SELECT count(1) FROM mail m\n" +
            "JOIN mailexplain mp ON m.id=mp.mailnumber\n" +
            "JOIN `user` u on m.receivingEmail=u.MailboxNo\n" +
            "WHERE  m.receivingEmail=#{receivingEmail} AND mp.mailstatus=0 AND ifRead=0")
    int shouCount(@Param("receivingEmail") String receivingEmail);

    /**
     * 星标邮件未读数量
     * @param
     * @return
     */
    @Select("SELECT count(1) FROM mail m\n" +
            "JOIN mailexplain mp ON m.id=mp.mailnumber\n" +
            "JOIN `user` u on m.receivingEmail=u.MailboxNo\n" +
            "WHERE m.receivingEmail=#{receivingEmail} AND mp.ifStarmarker=1 AND ifRead=0")
    int xingCount(@Param("receivingEmail") String receivingEmail);

    /**
     * 已发送未读数量
     * @param
     * @return
     */
    @Select("SELECT count(1) FROM mail m\n" +
            "JOIN mailexplain mp ON m.id=mp.mailnumber\n" +
            "JOIN `user` u ON m.receivingEmail=u.MailboxNo\n" +
            "WHERE m.receivingEmail=#{receivingEmail} AND mp.mailstatus=3 AND ifRead=0")
    int faCount(@Param("receivingEmail") String receivingEmail);


    /**
     * 已发送未读数量
     * @param
     * @return
     */
    @Select("SELECT count(1) FROM mail m\n" +
            "JOIN mailexplain mp ON m.id=mp.mailnumber\n" +
            "JOIN `user` u on m.receivingEmail=u.MailboxNo\n" +
            "WHERE m.receivingEmail=#{receivingEmail} AND mp.mailstatus=1 AND ifRead=0")
    int shanCount(@Param("receivingEmail") String receivingEmail);


    /**
     * 草稿箱统计
     * @param
     * @return
     */
    @Select("SELECT count(1) FROM mail m\n" +
            "JOIN mailexplain mp ON m.id=mp.mailnumber\n" +
            "JOIN `user` u on m.receivingEmail=u.MailboxNo\n" +
            "WHERE m.receivingEmail=#{receivingEmail} AND mp.mailstatus=4")
    int draftCount(@Param("receivingEmail") String receivingEmail);

    /**
     * 垃圾箱未读统计数量
     * @param
     * @return
     */
    @Select("SELECT count(1) FROM mail m\n" +
            "JOIN mailexplain mp ON m.id=mp.mailnumber\n" +
            "JOIN `user` u on m.receivingEmail=u.MailboxNo\n" +
            "WHERE m.receivingEmail=#{receivingEmail} AND mp.mailstatus=5 AND ifRead=0")
    int deleteCount(@Param("receivingEmail") String receivingEmail);




    /**
     * 查询收件箱里所有邮件数量
     * @param receivingEmail
     * @return
     */
    @Select("SELECT COUNT(1)  FROM mail m JOIN mailexplain mp\n" +
            " ON m.id=mp.id JOIN `user` u on m.ReceivingEmail=u.MailboxNo\n" +
            " WHERE ReceivingEmail =#{receivingEmail} AND mailstatus=0  ")
    int allCount(@Param("receivingEmail") String receivingEmail);


    /**
     * 收件箱分页查询
     * @param receivingEmail
     * @return
     */
    @Select("\n" +
            "SELECT m.*,mp.ifpassword AS 'mailexplain.ifpassword',ifreceipt AS 'mailexplain.ifreceipt', Encrypt_Password as 'mailexplain.Encrypt_Password', mp.mailstatus AS 'mailexplain.mailstatus', mp.ifStarmarker AS 'mailexplain.ifStarmarker',\n" +
            " mp.ifRead AS 'mailexplain.ifRead' ,u.username AS 'user.username',u.id as 'user.id',u1.username as 'user1.username'  FROM mail m JOIN mailexplain mp\n" +
            " ON m.id=mp.id JOIN `user` u on m.ReceivingEmail=u.MailboxNo\n" +
            " JOIN `user` u1 ON m.Sendnumber=u1.id\n" +
            " WHERE ReceivingEmail =#{receivingEmail} AND mailstatus=0  ORDER BY Receivingtime desc")
    List<Mail> selectMailStatus0ByPage(@Param("receivingEmail") String receivingEmail);


    /**
     * 改变多封邮件状态
     * @param mailnumber
     * @return
     */
    @Update("<script> " +
            " UPDATE mailexplain\n" +
            " set  mailstatus= #{mailstatus}\n" +
            " WHERE  mailnumber in\n" +
            " <foreach collection=\"mailnumber\" item=\"mailnumber\" index=\"index\"  open=\"(\" separator=\",\" close=\")\">\n" +
            "     #{mailnumber}\n" +
            " </foreach>" +
            " </script>")
    int  updateMailStatus(@Param("mailnumber") int [] mailnumber ,@Param("mailstatus") int mailstatus);


    /**
     * 将多封邮件标记为星标
     * @param mailnumber
     * @return
     */
    @Update("<script> " +
            " UPDATE mailexplain\n" +
            " set  ifStarmarker=1\n" +
            " WHERE  mailnumber in\n" +
            " <foreach collection=\"array\" item=\"mailnumber\" index=\"index\"  open=\"(\" separator=\",\" close=\")\">\n" +
            "     #{mailnumber}\n" +
            " </foreach>" +
            " </script>")
    int updateMailStarMarks(int [] mailnumber);


    /**
     * 将多封邮件取消星标
     * @param mailnumber
     * @return
     */
    @Update("<script> " +
            " UPDATE mailexplain\n" +
            " set  ifStarmarker=0\n" +
            " WHERE  mailnumber in\n" +
            " <foreach collection=\"array\" item=\"mailnumber\" index=\"index\"  open=\"(\" separator=\",\" close=\")\">\n" +
            "     #{mailnumber}\n" +
            " </foreach>" +
            " </script>")
    int updateMailStarMarks0(int [] mailnumber);

    /**
     * 取消或标记邮箱的星标
     * @param ifStarmarker
     * @param mailnumber
     * @return
     */
    @Update("UPDATE mailexplain SET ifStarmarker=#{ifStarmarker} WHERE mailnumber=#{mailnumber}")
    int updateMailStarMark(@Param("ifStarmarker") int ifStarmarker,@Param("mailnumber") int mailnumber);


    /**
     * 举报该邮箱(放入垃圾箱)
     * @param mailnumber
     * @return
     */
    @Update("  <script>UPDATE mailexplain\n" +
            " set  mailstatus=#{mailstatus}\n" +
            " WHERE  mailnumber in\n" +
            " <foreach collection=\"mailnumber\" item=\"mailnumber\" index=\"index\"  open=\"(\" separator=\",\" close=\")\">\n" +
            "     #{mailnumber}\n" +
            " </foreach></script>")
    int updateMailStatus5(@Param("mailnumber") int [] mailnumber,@Param("mailstatus") int mailstatus );

    /**
     * 将本页的邮箱全部改为已读状态
     * @param mailnumber
     * @return
     */
    @Update("<script>  UPDATE mailexplain\n" +
            " set  ifRead=1\n" +
            " WHERE  mailnumber in\n" +
            " <foreach collection=\"array\" item=\"mailnumber\" index=\"index\"  open=\"(\" separator=\",\" close=\")\">\n" +
            "     #{mailnumber}\n" +
            " </foreach></script>")
    int updateIfReadMail(int [] mailnumber);


    @Update("<script>  UPDATE mailexplain\n" +
            " set  ifRead=#{ifRead}\n" +
            " WHERE  mailnumber in\n" +
            " <foreach collection=\"mailnumber\" item=\"mailnumber\" index=\"index\"  open=\"(\" separator=\",\" close=\")\">\n" +
            "     #{mailnumber}\n" +
            " </foreach></script>")
    int updateIfReadMails(@Param("mailnumber") int [] mailnumber, @Param("ifRead") int ifRead);

}
